﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Data;
using Automobile.service.Model;

namespace Automobile.service.DAL
{
    public class ShipmentsDal
    {
        string str = "Data Source=.;Initial Catalog=qichefuwu;Integrated Security=True";

        /// <summary>
        /// 显示库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public List<ShipmentsModel> Shipments()
        {
            string sql = "select Shipments_Id,Shipments_blitems,Shipments_data,Shipments_Per,Shipments_Por,Shipments_Peosor,MemberCard_Id,WareHose_Id,Shipments_Price,Shipments_Pricepor,Shipments_Remark from Shipments";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<ShipmentsModel> list = new List<ShipmentsModel>();
            foreach (DataRow item in table.Rows)
            {
                ShipmentsModel s = new ShipmentsModel();
                s.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                s.Shipments_blitems = item["Shipments_blitems"].ToString();
                s.Shipments_data = Convert.ToDateTime(item["Shipments_data"]);
                s.Shipments_Per = item["Shipments_Per"].ToString();
                s.Shipments_Por = item["Shipments_Por"].ToString();
                s.Shipments_Peosor = item["Shipments_Peosor"].ToString();
                s.MemberCard_Id = Convert.ToInt32(item["MemberCard_Id"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Shipments_Price = Convert.ToInt32(item["Shipments_Price"]);
                s.Shipments_Pricepor = Convert.ToInt32(item["Shipments_Pricepor"]);
                s.Shipments_Remark = item["Shipments_Remark"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 添加库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public int AddShipments(ShipmentsModel s)
        {
            string sql = "insert into Shipments(Shipments_blitems,Shipments_data,Shipments_Per,Shipments_Por,Shipments_Peosor,MemberCard_Id,WareHose_Id,Shipments_Price,Shipments_Pricepor,Shipments_Remark) values ('" + s.Shipments_blitems + "','" + s.Shipments_data + "','" + s.Shipments_Per + "','" + s.Shipments_Por + "','" + s.Shipments_Peosor + "','" + s.MemberCard_Id + "','" + s.WareHose_Id + "','" + s.Shipments_Price + "','" + s.Shipments_Pricepor + "','" + s.Shipments_Remark + "')";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 删除库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public int DeleShipments(int Shipments_Id)
        {
            string sql = "delete from Shipments where Shipments_Id=" + Shipments_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }

        /// <summary>
        /// 取单个库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetShipments(string Shipments_Id)
        {
            string sql = "select Shipments_Id,Shipments_blitems,Shipments_data,Shipments_Per,Shipments_Por,Shipments_Peosor,MemberCard_Id,WareHose_Id,Shipments_Price,Shipments_Pricepor,Shipments_Remark from Shipments where Shipments_Id=" + Shipments_Id;

            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            DataTable table = new DataTable();
            SqlDataAdapter ada = new SqlDataAdapter(comm);
            ada.Fill(table);
            conn.Close();
            return table;
        }

        /// <summary>
        /// 更新库存盘点单信息
        /// </summary>
        /// <param name="stu"></param>
        /// <returns></returns>
        public int UpdateShipmentsModel(ShipmentsModel s)
        {
            string sql = "update Shipments set Shipments_blitems='"+s.Shipments_blitems+"',Shipments_data='"+s.Shipments_data+"',Shipments_Per='"+s.Shipments_Per+"',Shipments_Por='"+s.Shipments_Por+"',Shipments_Peosor='"+s.Shipments_Peosor+"',MemberCard_Id='"+s.MemberCard_Id+"',WareHose_Id='"+s.WareHose_Id+"',Shipments_Price='"+s.Shipments_Price+"',Shipments_Pricepor='"+s.Shipments_Pricepor+"',Shipments_Remark='"+s.Shipments_Remark+"' where Shipments_Id=" + s.Shipments_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            //执行命令
            int count = comm.ExecuteNonQuery();
            conn.Close();

            return count;
        }
    }
}
